Open topic with navigation

You are here: How To Guides > Server Configuration > SQL Server Maintenance Plan

 

SQL Server Maintenance Plan

Your KommerceServer site modifies the SQL Server database as your customers use it and as data feeds are imported from the back office. Over time, this usage fragments the indexes and statistics that SQL Server uses to maintain optimal performance. If left unattended, this fragmentation will cause site performance to degrade significantly. To ensure that your site continues to operate at optimum performance, you must perform periodic maintenance on the SQL Server database.

The procedure outlined in this topic is a recommendation. You are free to administer your SQL Server database in any way you choose.
It is not necessary to setup the maintenance plan for the test database delivered as part of KommerceServer. Only the production database needs to be setup.

Overview

This maintenance plan uses a pair of SQL Server Agent jobs that are scheduled to run periodically in the background:

Index Optimization - This job optimizes table indexes and clears the query plan cache. This helps to ensure the data look-ups operate at peak efficiency.

Update Statistics - This job updates optimization statistics on tables and views. This helps the SQL Server query optimizer to compile queries with up-to-date statistics.

These jobs call T-SQL script commands that optimize the indexes and statistics. These commands consist of a SQL Server Maintenance Solution provided free-of-charge by Ola Hallengren.

Install Ola Hallengren’s SQL Server Maintenance Solution

To use the SQL Server Maintenance Solution, you must download the script file and install it on your SQL Server instance.

To download the script file

  1. Use a web browser and navigate to https://ola.hallengren.com/.
  2. In the Getting Started section, click the MaintenanceSolution.sql link.
  3. Save the MaintenanceSolution.sql file to disk.

To install the solution

  1. Open SQL Server Management Studio.
  2. Connect to the SQL Server database engine that contains the KommerceServer database.
  3. From the File menu, click Open, then click File.
  4. Locate the MaintenanceSolution.sql file and click Open.
  5. From the Query menu, click Execute.

To ensure the maintenance solution is installed

  1. Open the SQL Server Management Studio Object Explorer.
  2. Expand the Stored Procedures node under the master system database.
  3. Ensure the IndexOptimize command exists.

Configuring the Index Optimization Job

This section describes the process for configuring the index optimization SQL Server Agent job.

To configure the index optimization job:

  1. Open the SQL Server Management Studio Object Explorer.
  2. Expand the Jobs node under the SQL Server Agent node.
  3. Right-click Jobs, then click New Job. This opens the General page for a new job.
  4. Configure the General page as shown below:
  5. In the Select a page panel, click Steps.
  6. Click New to create step 1.
  7. In the Step name field, enter OptimizeIndex.
  8. In the Command field, enter the following command text:
    EXECUTE dbo.IndexOptimize
    @Databases = 'kommerceserver_yourcompany',
    @FragmentationLow = 'INDEX_REORGANIZE',
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @PageCountLevel = 1,
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y'
  9. In the Command field, change the name of the @Databases parameter from kommerceserver_yourcompany to the name of your KommerceServer database.
  10. Click OK to save the step and close the step properties window.
  11. Click New to create step 2.
  12. In the Step name field, enter Clear Query Plan.
  13. In the Command field, enter the following command text:
    DBCC FREEPROCCACHE
  14. Click OK to save the step and close the step properties window.
  15. In the Select a page panel, click Schedules.
  16. Click New to create a new schedule.
  17. Configure the schedule. The schedule shown below causes the job to run nightly at 11:00PM.
  18. Click OK to save the schedule and close the schedule window.

Configuring the Update Statistics Job

This section describes the process for configuring the update statistics SQL Server Agent job.

To configure the update statistics job:

  1. Open the SQL Server Management Studio Object Explorer.
  2. Expand the Jobs node under the SQL Server Agent node.
  3. Right-click Jobs, then click New Job. This opens the General page for a new job.
  4. Configure the General page as shown below:
  5. In the Select a page panel, click Steps.
  6. Click New to create step 1.
  7. In the Step name field, enter UpdateStats.
  8. In the Command field, enter the following command text:
    EXECUTE dbo.IndexOptimize
    @Databases = 'kommerceserver_yourcompany',
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL'
  9. In the Command field, change the name of the @Databases parameter from kommerceserver_yourcompany to the name of your KommerceServer database.
  10. Click OK to save the step and close the step properties window.
  11. In the Select a page panel, click Schedules.
  12. Click New to create a new schedule.
  13. Configure the schedule. The schedule shown below causes the job to run every 6 hours.
  14. Click OK to save the schedule and close the schedule window.